﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VNPT.Utility.Profile;
using System.Windows.Forms;

namespace VNPT.DoiSoat
{
    public static class DoiSoatSetting
    {
        #region BC 1088
        public static string SQL_BAOCAO_1088
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_1088", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_1088", value);
                }
            }
        }
        #endregion

        #region BC GTGT
       
        public static string SQL_BAOCAO_TH_GTGT_THUEBAO
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_THUEBAO", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_THUEBAO", value);
                }
            }
        }
        public static string SQL_BAOCAO_TH_GTGT
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT", value);
                }
            }
        }
        public static string SQL_BAOCAO_TH_GTGT_TIEN
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_TIEN", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_TIEN", value);
                }
            }
        }
        #endregion

        #region Báo cáo GTGT Năm
        public static string SQL_BAOCAO_TH_GTGT_NAM_THUEBAO
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM_THUEBAO", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM_THUEBAO", value);
                }
            }
        }
        public static string SQL_BAOCAO_TH_GTGT_NAM
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM", value);
                }
            }
        }
        public static string SQL_BAOCAO_TH_GTGT_NAM_TIEN
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM_TIEN", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_BAOCAO_TH_GTGT_NAM_TIEN", value);
                }
            }
        }
        #endregion
        

        #region Biểu 115 VTT6_GTGT
        public static string SQL_VTT6_GTGT_108_801
        {
            get
            {

                string sqlString = "select dv,count(*) block_60s,sum(case when tggoi>60 then tggoi-60 else 0 end) block_1s from\n" +
                "  (\n" +
                "  select case\n" +
                "  when (substr(bigoi,1,3) in ('108') or substr(bigoi,1,6) in ('500108')or substr(bigoi,1,10) in ('1710500108')) and ma_mang ='VTLMOBI'\n" +
                "        then 'Didong_108x'\n" +
                "       when (substr(bigoi,1,3) in ('801') or substr(bigoi,1,6) in ('500801')or substr(bigoi,1,10) in ('1710500801')) and ma_mang ='VTLMOBI'\n" +
                "         then 'Didong_801x'\n" +
                "\n" +
                "       when (substr(bigoi,1,3) in ('108') or substr(bigoi,1,6) in ('500108')) and substr(ma_mang,1,3)='VTL'\n" +
                "        then 'Codinh_108x'\n" +
                "       when (substr(bigoi,1,3) in ('801') or substr(bigoi,1,6) in ('500801')) and substr(ma_mang,1,3)='VTL'\n" +
                "        then 'Codinh_801x'\n" +
                "\n" +
                "       end dv,bigoi,tggoi,chugoi, ma_mang\n" +
                "  from  doisoat.CT_HOST_810_{0}_{1:D2}A_DLK \n" +
                " where (substr(ma_mang,1,3)='VTL' or ma_mang='VTLMOBI') and out_route in ('D108O','DV108O') and inc_route not in ('H501I','HOST1I')\n" +
                " and bigoi not in ('1163','116','3843900','500116','1160')  and tggoi>2\n" +
                " )\n" +
                "group by dv\n" +
                "order by dv";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_VTT6_GTGT_108_801", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_VTT6_GTGT_108_801", value);
                }
            }
        }
        public static string SQL_VTT6_GTGT_106
        {
            get
            {

                string sqlString = "select dv,sum(blk_60s) block_60s,sum(blk_1s) block_1s from\n" +
                "(\n" +
                "select case\n" +
                "                  when service_id in (901,904) then '1060'\n" +
                "                  when service_id not in (901,904) then '106x'\n" +
                "           end dv,case when tg_goi<>0 then 1 end blk_60s, case when tg_goi>60 then tg_goi-60 else 0 end blk_1s\n" +
                "\n" +
                "from daklak.ctdt_{0}{1:D2}01\n" +
                "where service_id between 901 and 912\n" +
                ") group by dv";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_VTT6_GTGT_106", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_VTT6_GTGT_106", value);
                }
            }
        }
        #endregion


        #region Lấy dữ liệu 178 Viettel
        public static string SQL_DV_178_Viettel
        {
            get
            {
                string sqlString = @"SELECT CHUGOI AS calling, BIGOI AS called, NGAYBD, GIOBD, TGGOI AS TGLL, INC_ROUTE, OUT_ROUTE
                                    FROM doisoat.CT_HOST_810_{0}_{1:D2}A_DLK@sunsky_6 WHERE  bigoi like '178%'";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_DV_178_Viettel", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_DV_178_Viettel", value);
                }
            }
        }
        #endregion


        #region Biểu 114
        public static string SQL_DV_1800_1900DLK_DNK
        {
            get
            {
                string sqlString = "select sum(phut) Phut from ( Select phut,bigoi from ct_host_810_{0}_{1:D2}a_dlk  where bigoi LIKE '{2}' union all Select phut,bigoi from ct_gphone_{0}_{1:D2}_dlk  where bigoi LIKE '{2}')";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_DV_1800_1900DLK_DNK", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_DV_1800_1900DLK_DNK", value);
                }
            }
        }
        #endregion

        #region Bieu 109
        public static string SQL_DV_1800_1900_VTT_VTN
        {
            get
            {

                string sqlString = "SELECT sum(CEIL(tg_goi/60)) FROM (SELECT tg_goi , bigoi FROM daklak.ctdt_{0}{1:D2}01 WHERE bigoi LIKE '{2}%' )";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_DV_1800_1900_VTT_VTN", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_DV_1800_1900_VTT_VTN", value);
                }

            }
        }

        public static string SQL_DV_1800_1900_VTT_DNK
        {
            get
            {

                string sqlString = "SELECT dvbb , sum ( phut ) AS Phut , count ( * ) Cuoc\n" +
                "FROM ct_host_810_{0}_{1:D2}a_dlk\n" +
                "WHERE inc_route NOT IN ( 'HOST1I' , 'H501I' ) AND ma_mang = 'VMS' AND\n" +
                "( bigoi LIKE '108%' OR bigoi LIKE '801%' OR bigoi LIKE '802%' OR\n" +
                "    bigoi LIKE '500108%' OR bigoi LIKE '500801%' OR bigoi LIKE '500802%' OR\n" +
                "    bigoi LIKE '1710500108%' OR bigoi LIKE '1710500801%' OR bigoi LIKE '1710500802%'\n" +
                ")\n" +
                "GROUP BY dvbb";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_DV_1800_1900_VTT_DNK", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_DV_1800_1900_VTT_DNK", value);
                }

            }
        }
        #endregion

        #region Biểu 108 đối soát GTGT giữa VMS với VTT
        public static string SQL_GTGT_VMS_TOLL_VTN
        {
            get
            {

                string sqlString = "SELECT dvbb , sum ( phut ) AS Phut , count ( * ) Cuoc\n" +
                "FROM ct_host_810_{0}_{1:D2}a_dlk\n" +
                "WHERE inc_route NOT IN ( 'HOST1I' , 'H501I' ) AND ma_mang = 'VMS' AND\n" +
                "( bigoi LIKE '108%' OR bigoi LIKE '801%' OR bigoi LIKE '802%' OR\n" +
                "    bigoi LIKE '500108%' OR bigoi LIKE '500801%' OR bigoi LIKE '500802%' OR\n" +
                "    bigoi LIKE '1710500108%' OR bigoi LIKE '1710500801%' OR bigoi LIKE '1710500802%'\n" +
                ")\n" +
                "GROUP BY dvbb";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_VMS_TOLL_VTN", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_VMS_TOLL_VTN", value);
                }

            }
        }
        #endregion

        #region Biểu 106 Đối soát GTGT giữa VTT với các đơn vị trong VNPT
        public static string SQL_GTGT_GOI_DEN_DLK
        {
            get
            {
                string sqlString = "select * from (select ma_mang, count(*) as CUOC, sum(phut) as PHUT \n" +
                "  from ct_host_810_{0}_{1:D2}a_dlk\n" +
                "  where  inc_route not in('HOST1I','H501I') and\n" +
                "         Ma_mang not like '%DLK%' and ma_mang not like '%VMS%' and ma_mang not like '%VTL%' and\n" +
                "         (bigoi like '108%' or bigoi like '801%' or bigoi like '802%' or bigoi like '1089%' or\n" +
                "         bigoi like '500108%' or bigoi like '500801%' or bigoi like '500802%' or bigoi like '5001089%' or\n" +
                "         bigoi like '1710500108%' or bigoi like '1710500801%' or bigoi like '1710500802%' or bigoi like '17105001089%')\n" +
                "  group by ma_mang)\n" +
                "Where Ma_mang in (select Ma_Mang from mavung union all select Ma_Mang||'GPH' as Ma_Mang from mavung)";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_GOI_DEN_DLK", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_GOI_DEN_DLK", value);
                }

            }
        }

        public static string SQL_GTGT_VINAPHONE_GOI_DEN_DLK
        {
            get
            {
                string sqlString = "Select dvbb, sum(phut) as Phut, count(*) Cuoc from ct_host_810_{0}_{1:D2}a_dlk\n" +
                "where inc_route not in('HOST1I','H501I') and  ma_mang ='GPC' and\n" +
                "       ( bigoi like '108%' or bigoi like '801%'  or bigoi like '802%' or\n" +
                "         bigoi like '500108%'  or bigoi like '500801%'  or bigoi like '500802%' or\n" +
                "         bigoi like '1710500108%'  or bigoi like '1710500801%'  or bigoi like '1710500802%'\n" +
                "       )\n" +
                "group by dvbb";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_VINAPHONE_GOI_DEN_DLK", sqlString);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_VINAPHONE_GOI_DEN_DLK", value);
                }

            }
        }
        #region Chiều đến

        #endregion
        #region Chiều đi
        public static string SQL_GTGT_GOI_DI_108x
        {
            get
            {
                var sql = @"select count(*) as Cuoc, sum(phut) As Phut from ct_host_810_{0}_{1:D2}a_dlk where  bigoi like '{2}108%'";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_108x", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    //Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_108x", value);
                }

            }
        }
      
        public static string SQL_GTGT_GOI_DI_801x
        {
            get
            {
                var sql = @"select count(*) as Cuoc, sum(phut) As Phut from ct_host_810_{0}_{1:D2}a_dlk where  bigoi like '{2}801%'";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_801x", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    //Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_801x", value);
                }

            }
        }
       
        public static string SQL_GTGT_GOI_DI_802x
        {
            get
            {
                var sql = @"select count(*) as Cuoc, sum(phut) as Phut from ct_host_810_{0}_{1:D2}a_dlk where  bigoi like '{2}802%'";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_802x", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    //Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_802x", value);
                }

            }
        }
       
        public static string SQL_GTGT_GOI_DI_1089x
        {
            get
            {
                var sql = @"select count(*) as Cuoc, sum(phut) as Phut from ct_host_810_{0}_{1:D2}a_dlk where  bigoi like '{2}1089%'";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_1089x", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    //Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GTGT_GOI_DI_1089x", value);
                }

            }
        }
        #endregion
        #endregion

        public static string SQL_GOI_DEN_CO_DINH_DLK
        {
            get
            {
                var sql=@"select ceil(sum(tggoi/60)) as Phut, Ma_Mang from(" +
                                "      select TGGoi,Phut, Ma_Mang, chugoi from doisoat.ct_host_810_{0}_{1:D2}a_dlk\n" +
                                "      Where (bigoi like '3%' or bigoi like '5003%'or bigoi like '17105003%') and\n" +
                                "            (bigoi not like '350%' and\n" +
                                "            bigoi not like '360%' and\n" +
                                "            bigoi not like '370%' and\n" +
                                "            bigoi not like '391%' and\n" +
                                "            bigoi not like '85%' and\n" +
                                "            bigoi not like '500350%' and\n" +
                                "            bigoi not like '500360%' and\n" +
                                "            bigoi not like '500370%' and\n" +
                                "            bigoi not like '50091%' and\n" +
                                "            bigoi not like '50085%'and\n" +
                                "            bigoi not like '1710500350%' and\n" +
                                "            bigoi not like '1710500360%' and\n" +
                                "            bigoi not like '1710500370%' and\n" +
                                "            bigoi not like '171050091%' and\n" +
                                "            bigoi not like '171050085%')\n" +
                                "            And Ma_mang not like '%DLK%' and\n" +
                                "                Ma_mang not like '%VTL%' and\n" +
                                "                Ma_mang not like '%GPC%' and\n" +
                                "                Ma_mang not like '%HTC%' and\n" +
                                "                Ma_mang not like '%VMS%'\n" +
                                "      Union all\n" +
                                "      select TGGoi,Phut, Ma_Mang, chugoi from doisoat.ct_host_810_{0}_{1:D2}a_dlk\n" +
                                "      Where (bigoi like '3%' or bigoi like '5003%'or bigoi like '17105003%') and\n" +
                                "            (bigoi not like '350%' and\n" +
                                "            bigoi not like '360%' and\n" +
                                "            bigoi not like '370%' and\n" +
                                "            bigoi not like '391%' and\n" +
                                "            bigoi not like '85%' and\n" +
                                "            bigoi not like '500350%' and\n" +
                                "            bigoi not like '500360%' and\n" +
                                "            bigoi not like '500370%' and\n" +
                                "            bigoi not like '50091%' and\n" +
                                "            bigoi not like '50085%'and\n" +
                                "            bigoi not like '1710500350%' and\n" +
                                "            bigoi not like '1710500360%' and\n" +
                                "            bigoi not like '1710500370%' and\n" +
                                "            bigoi not like '171050091%' and\n" +
                                "            bigoi not like '171050085%')\n" +
                                "            And Ma_mang not like '%DLK%' and\n" +
                                "                Ma_mang not like '%VTL%' and\n" +
                                "                Ma_mang not like '%GPC%' and\n" +
                                "                Ma_mang not like '%HTC%' and\n" +
                                "                Ma_mang not like '%VMS%')\n" +
                                "group by Ma_mang";
                //Xml xml = new Xml();
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GOI_DEN_CO_DINH_DLK", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    //Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GOI_DEN_CO_DINH_DLK", value);
                }

            }
        }

        public static string SQL_GOI_DEN_GPHONE_DLK
        {
            get
            {
                var sql = @"select ceil(sum(tggoi/60))  as Phut, Ma_Mang from(" +
                                "    select TGGoi,Phut, Ma_Mang from doisoat.ct_host_810_{0}_{1:D2}a_dlk\n" +
                                "    Where (bigoi like '350%' or\n" +
                                "          bigoi like '360%' or\n" +
                                "          bigoi like '370%' or\n" +
                                "          bigoi like '391%' or\n" +
                                "          bigoi like '85%' or\n" +
                                "          bigoi like '500350%' or\n" +
                                "          bigoi like '500360%' or\n" +
                                "          bigoi like '500370%' or\n" +
                                "          bigoi like '50091%' or\n" +
                                "          bigoi like '50085%'or\n" +
                                "          bigoi like '1710500350%' or\n" +
                                "          bigoi like '1710500360%' or\n" +
                                "          bigoi like '1710500370%' or\n" +
                                "          bigoi like '171050091%' or\n" +
                                "          bigoi like '171050085%')\n" +
                                "          And Ma_mang not like '%DLK%' and\n" +
                                "              Ma_mang not like '%VTL%' and\n" +
                                "              Ma_mang not like '%VMS%'\n" +
                                "    Union All\n" +
                                "    select TGGoi,Phut, Ma_Mang from doisoat.ct_host_TanLoi_{0}_{1:D2}a_dlk\n" +
                                "    Where (bigoi like '350%' or\n" +
                                "          bigoi like '360%' or\n" +
                                "          bigoi like '370%' or\n" +
                                "          bigoi like '391%' or\n" +
                                "          bigoi like '85%' or\n" +
                                "          bigoi like '500350%' or\n" +
                                "          bigoi like '500360%' or\n" +
                                "          bigoi like '500370%' or\n" +
                                "          bigoi like '50091%' or\n" +
                                "          bigoi like '50085%'or\n" +
                                "          bigoi like '1710500350%' or\n" +
                                "          bigoi like '1710500360%' or\n" +
                                "          bigoi like '1710500370%' or\n" +
                                "          bigoi like '171050091%' or\n" +
                                "          bigoi like '171050085%')\n" +
                                "          And Ma_mang not like '%DLK%' and\n" +
                                "              Ma_mang not like '%VTL%' and\n" +
                                "              Ma_mang not like '%VMS%'\n" +
                                ") group by Ma_mang";
                //Xml xml = new Xml();
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GOI_DEN_GPHONE_DLK", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                   // Xml xml = new Xml();
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GOI_DEN_GPHONE_DLK", value);
                }

            }
        }

        public static string SQL_GPHONE_DLK_GOI_DI
        {
            get
            {
                var sql = "select ceil(sum(tggoi/60))  as PHUT, Ma_Mang  from(\n" +
            "          select Phut, doisoat.getmamang(doisoat.loaibo_so_0(doisoat.loaibo_so_171(bigoi))) as Ma_Mang, TGGoi\n" +
            "          from ct_gphone_{0}_{1:D2}_dlk\n" +
            "          where bigoi Like '0763%' Or bigoi Like '1710763%' Or bigoi Like '0768%' Or bigoi Like '1710768%' --AGG\n" +
            "            Or bigoi Like '0643%'  Or bigoi Like '1710643%'  Or bigoi Like '0648%'  Or bigoi Like '1710648%' --VTU\n" +
            "            Or bigoi Like '06503%' Or bigoi Like '17106503%' Or bigoi Like '06508%' Or bigoi Like '17106508%' --BDG\n" +
            "            Or bigoi Like '06513%' Or bigoi Like '17106513%' Or bigoi Like '06518%' Or bigoi Like '17106518%' --BPC\n" +
            "            Or bigoi Like '0623%'  Or bigoi Like '1710623%'  Or bigoi Like '0628%'  Or bigoi Like '1710628%' --BTN\n" +
            "            Or bigoi Like '0563%'  Or bigoi Like '1710563%'  Or bigoi Like '0568%'  Or bigoi Like '1710568%' --BDH\n" +
            "            Or bigoi Like '07813%' Or bigoi Like '17107813%' Or bigoi Like '07818%' Or bigoi Like '17107818%' --BLU\n" +
            "            Or bigoi Like '02813%' Or bigoi Like '17102813%' Or bigoi Like '02818%' Or bigoi Like '17102818%' --BCN\n" +
            "            Or bigoi Like '02403%' Or bigoi Like '17102403%' Or bigoi Like '02408%' Or bigoi Like '17102408%' --BGG\n" +
            "            Or bigoi Like '02413%' Or bigoi Like '17102413%' Or bigoi Like '02418%' Or bigoi Like '17102418%' --BNH\n" +
            "            Or bigoi Like '0753%'  Or bigoi Like '1710753%'  Or bigoi Like '0758%'  Or bigoi Like '1710758%' --BTE\n" +
            "            Or bigoi Like '0263%'  Or bigoi Like '1710263%'  Or bigoi Like '0268%'  Or bigoi Like '1710268%' --CBG\n" +
            "            Or bigoi Like '07803%' Or bigoi Like '17107803%' Or bigoi Like '07808%' Or bigoi Like '17107808%' --CMU\n" +
            "            Or bigoi Like '07103%' Or bigoi Like '17107103%' Or bigoi Like '07108%' Or bigoi Like '17107108%' --CTO\n" +
            "            Or bigoi Like '07113%' Or bigoi Like '17107113%' Or bigoi Like '07118%' Or bigoi Like '17107118%' --HGG\n" +
            "            Or bigoi Like '0593%'  Or bigoi Like '1710593%'  Or bigoi Like '0598%'  Or bigoi Like '1710598%' --GLI\n" +
            "            Or bigoi Like '02193%' Or bigoi Like '17102193%' Or bigoi Like '02198%' Or bigoi Like '17102198%' --HGG\n" +
            "            Or bigoi Like '03513%' Or bigoi Like '17103513%' Or bigoi Like '03518%' Or bigoi Like '17103518%' --HNM\n" +
            "            Or bigoi Like '043%'   Or bigoi Like '171043%'   Or bigoi Like '048%'   Or bigoi Like '171048%' --HNI\n" +
            "            Or bigoi Like '0393%'  Or bigoi Like '1710393%'  Or bigoi Like '0398%'  Or bigoi Like '1710398%' --HTH\n" +
            "            Or bigoi Like '02183%' Or bigoi Like '17102183%' Or bigoi Like '02188%' Or bigoi Like '17102188%' --HBH\n" +
            "            Or bigoi Like '03213%' Or bigoi Like '17103213%' Or bigoi Like '03218%' Or bigoi Like '17103218%' --HYN\n" +
            "            Or bigoi Like '03203%' Or bigoi Like '17103203%' Or bigoi Like '03208%' Or bigoi Like '17103208%' --HDG\n" +
            "            Or bigoi Like '0313%'  Or bigoi Like '1710313%'  Or bigoi Like '0318%'  Or bigoi Like '1710318%' --HPG\n" +
            "            Or bigoi Like '0583%'  Or bigoi Like '1710583%'  Or bigoi Like '0588%'  Or bigoi Like '1710588%' --KHA\n" +
            "            Or bigoi Like '0773%'  Or bigoi Like '1710773%'  Or bigoi Like '0778%'  Or bigoi Like '1710778%' --KGG\n" +
            "            Or bigoi Like '0603%'  Or bigoi Like '1710603%'  Or bigoi Like '0608%'  Or bigoi Like '1710608%' --KTM\n" +
            "            Or bigoi Like '02313%' Or bigoi Like '17102313%' Or bigoi Like '02318%' Or bigoi Like '17102318%' --LCU\n" +
            "            Or bigoi Like '02303%' Or bigoi Like '17102303%' Or bigoi Like '02308%' Or bigoi Like '17102308%' --DBN\n" +
            "            Or bigoi Like '0723%'  Or bigoi Like '1710723%'  Or bigoi Like '0728%'  Or bigoi Like '1710728%' --LAN\n" +
            "            Or bigoi Like '0203%'  Or bigoi Like '1710203%'  Or bigoi Like '0208%'  Or bigoi Like '1710208%' --LCI\n" +
            "            Or bigoi Like '0633%'  Or bigoi Like '1710633%'  Or bigoi Like '0638%'  Or bigoi Like '1710638%' --LDG\n" +
            "            Or bigoi Like '0253%'  Or bigoi Like '1710253%'  Or bigoi Like '0258%'  Or bigoi Like '1710258%' --LSN\n" +
            "            Or bigoi Like '03503%' Or bigoi Like '17103503%' Or bigoi Like '03508%' Or bigoi Like '17103508%' --NDH\n" +
            "            Or bigoi Like '0383%'  Or bigoi Like '1710383%'  Or bigoi Like '0388%'  Or bigoi Like '1710388%' --NAN\n" +
            "            Or bigoi Like '0303%'  Or bigoi Like '1710303%'  Or bigoi Like '0308%'  Or bigoi Like '1710308%' --NBH\n" +
            "            Or bigoi Like '0683%'  Or bigoi Like '1710683%'  Or bigoi Like '0688%'  Or bigoi Like '1710688%' --NTN\n" +
            "            Or bigoi Like '02103%' Or bigoi Like '17102103%' Or bigoi Like '02108%' Or bigoi Like '17102108%' --PTO\n" +
            "            Or bigoi Like '0573%'  Or bigoi Like '1710573%'  Or bigoi Like '0578%'  Or bigoi Like '1710578%' --PYN\n" +
            "            Or bigoi Like '0523%'  Or bigoi Like '1710523%'  Or bigoi Like '0528%'  Or bigoi Like '1710528%' --QBH\n" +
            "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --QNM\n" +
            "            Or bigoi Like '0553%'  Or bigoi Like '1710553%'  Or bigoi Like '0558%'  Or bigoi Like '1710558%' --QNI\n" +
            "            Or bigoi Like '0333%'  Or bigoi Like '1710333%'  Or bigoi Like '0338%'  Or bigoi Like '1710338%' --QNH\n" +
            "            Or bigoi Like '0533%'  Or bigoi Like '1710533%'  Or bigoi Like '0538%'  Or bigoi Like '1710538%' --QTI\n" +
            "            Or bigoi Like '0793%'  Or bigoi Like '1710793%'  Or bigoi Like '0798%'  Or bigoi Like '1710798%' --STG\n" +
            "            Or bigoi Like '0223%'  Or bigoi Like '1710223%'  Or bigoi Like '0228%'  Or bigoi Like '1710228%' --SLA\n" +
            "            Or bigoi Like '083%'   Or bigoi Like '171083%'   Or bigoi Like '088%'   Or bigoi Like '171088%' --HCM\n" +
            "            Or bigoi Like '0373%'  Or bigoi Like '1710373%'  Or bigoi Like '0378%'  Or bigoi Like '1710378%' --THA\n" +
            "            Or bigoi Like '0363%'  Or bigoi Like '1710363%'  Or bigoi Like '0368%'  Or bigoi Like '1710368%' --TBH\n" +
            "            Or bigoi Like '02803%' Or bigoi Like '17102803%' Or bigoi Like '02808%' Or bigoi Like '17102808%' --TNN\n" +
            "            Or bigoi Like '0543%'  Or bigoi Like '1710543%'  Or bigoi Like '0548%'  Or bigoi Like '1710548%' --HUE\n" +
            "            Or bigoi Like '0733%'  Or bigoi Like '1710733%'  Or bigoi Like '0738%'  Or bigoi Like '1710738%' --TGG\n" +
            "            Or bigoi Like '0743%'  Or bigoi Like '1710743%'  Or bigoi Like '0748%'  Or bigoi Like '1710748%' --TVH\n" +
            "            Or bigoi Like '0273%'  Or bigoi Like '1710273%'  Or bigoi Like '0278%'  Or bigoi Like '1710278%' --TQG\n" +
            "            Or bigoi Like '0663%'  Or bigoi Like '1710663%'  Or bigoi Like '0668%'  Or bigoi Like '1710668%' --TNH\n" +
            "            Or bigoi Like '0703%'  Or bigoi Like '1710703%'  Or bigoi Like '0708%'  Or bigoi Like '1710708%' --VLG\n" +
            "            Or bigoi Like '02113%' Or bigoi Like '17102113%' Or bigoi Like '02118%' Or bigoi Like '17102118%' --VPC\n" +
            "            Or bigoi Like '0293%'  Or bigoi Like '1710293%'  Or bigoi Like '0298%'  Or bigoi Like '1710298%' --YBI\n" +
            "            Or bigoi Like '05113%' Or bigoi Like '17105113%' Or bigoi Like '05118%' Or bigoi Like '17105118%' --DKG\n" +
            "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --DNG\n" +
            "            Or bigoi Like '0613%'  Or bigoi Like '1710613%'  Or bigoi Like '0618%'  Or bigoi Like '1710618%' --DNI\n" +
            "            Or bigoi Like '0673%'  Or bigoi Like '1710673%'  Or bigoi Like '0678%'  Or bigoi Like '1710678%' --DTP\n" +
            ")\n" +
            "Group by Ma_mang";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_GPHONE_DLK_GOI_DI", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
   
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_GPHONE_DLK_GOI_DI", value);
                }

            }
        }
      
        public static string SQL_CO_DINH_DLK_GOI_DI
        {
            get
            {
                var sql = "Select ceil(sum(tggoi/60)) as PHUT, Ma_Mang_BG as Ma_Mang from (\n" +
                                    "select TGGOI,PHUT, doisoat.getmamang(doisoat.loaibo_so_0(doisoat.loaibo_so_171(bigoi))) Ma_Mang_BG\n" +
                                    "     from doisoat.ct_host_810_{0}_{1:D2}a_dlk\n" +
                                    "     where chugoi not in (select somay from doisoat.danhba_nghiepvu)\n" +
                                    "           And ma_mang='DLK' and(\n" +
                                    "            bigoi Like '0763%' Or bigoi Like '1710763%' Or bigoi Like '0768%' Or bigoi Like '1710768%' --AGG\n" +
                                    "            Or bigoi Like '0643%'  Or bigoi Like '1710643%'  Or bigoi Like '0648%'  Or bigoi Like '1710648%' --VTU\n" +
                                    "            Or bigoi Like '06503%' Or bigoi Like '17106503%' Or bigoi Like '06508%' Or bigoi Like '17106508%' --BDG\n" +
                                    "            Or bigoi Like '06513%' Or bigoi Like '17106513%' Or bigoi Like '06518%' Or bigoi Like '17106518%' --BPC\n" +
                                    "            Or bigoi Like '0623%'  Or bigoi Like '1710623%'  Or bigoi Like '0628%'  Or bigoi Like '1710628%' --BTN\n" +
                                    "            Or bigoi Like '0563%'  Or bigoi Like '1710563%'  Or bigoi Like '0568%'  Or bigoi Like '1710568%' --BDH\n" +
                                    "            Or bigoi Like '07813%' Or bigoi Like '17107813%' Or bigoi Like '07818%' Or bigoi Like '17107818%' --BLU\n" +
                                    "            Or bigoi Like '02813%' Or bigoi Like '17102813%' Or bigoi Like '02818%' Or bigoi Like '17102818%' --BCN\n" +
                                    "            Or bigoi Like '02403%' Or bigoi Like '17102403%' Or bigoi Like '02408%' Or bigoi Like '17102408%' --BGG\n" +
                                    "            Or bigoi Like '02413%' Or bigoi Like '17102413%' Or bigoi Like '02418%' Or bigoi Like '17102418%' --BNH\n" +
                                    "            Or bigoi Like '0753%'  Or bigoi Like '1710753%'  Or bigoi Like '0758%'  Or bigoi Like '1710758%' --BTE\n" +
                                    "            Or bigoi Like '0263%'  Or bigoi Like '1710263%'  Or bigoi Like '0268%'  Or bigoi Like '1710268%' --CBG\n" +
                                    "            Or bigoi Like '07803%' Or bigoi Like '17107803%' Or bigoi Like '07808%' Or bigoi Like '17107808%' --CMU\n" +
                                    "            Or bigoi Like '07103%' Or bigoi Like '17107103%' Or bigoi Like '07108%' Or bigoi Like '17107108%' --CTO\n" +
                                    "            Or bigoi Like '07113%' Or bigoi Like '17107113%' Or bigoi Like '07118%' Or bigoi Like '17107118%' --HGG\n" +
                                    "            Or bigoi Like '0593%'  Or bigoi Like '1710593%'  Or bigoi Like '0598%'  Or bigoi Like '1710598%' --GLI\n" +
                                    "            Or bigoi Like '02193%' Or bigoi Like '17102193%' Or bigoi Like '02198%' Or bigoi Like '17102198%' --HGG\n" +
                                    "            Or bigoi Like '03513%' Or bigoi Like '17103513%' Or bigoi Like '03518%' Or bigoi Like '17103518%' --HNM\n" +
                                    "            Or bigoi Like '043%'   Or bigoi Like '171043%'   Or bigoi Like '048%'   Or bigoi Like '171048%' --HNI\n" +
                                    "            Or bigoi Like '0393%'  Or bigoi Like '1710393%'  Or bigoi Like '0398%'  Or bigoi Like '1710398%' --HTH\n" +
                                    "            Or bigoi Like '02183%' Or bigoi Like '17102183%' Or bigoi Like '02188%' Or bigoi Like '17102188%' --HBH\n" +
                                    "            Or bigoi Like '03213%' Or bigoi Like '17103213%' Or bigoi Like '03218%' Or bigoi Like '17103218%' --HYN\n" +
                                    "            Or bigoi Like '03203%' Or bigoi Like '17103203%' Or bigoi Like '03208%' Or bigoi Like '17103208%' --HDG\n" +
                                    "            Or bigoi Like '0313%'  Or bigoi Like '1710313%'  Or bigoi Like '0318%'  Or bigoi Like '1710318%' --HPG\n" +
                                    "            Or bigoi Like '0583%'  Or bigoi Like '1710583%'  Or bigoi Like '0588%'  Or bigoi Like '1710588%' --KHA\n" +
                                    "            Or bigoi Like '0773%'  Or bigoi Like '1710773%'  Or bigoi Like '0778%'  Or bigoi Like '1710778%' --KGG\n" +
                                    "            Or bigoi Like '0603%'  Or bigoi Like '1710603%'  Or bigoi Like '0608%'  Or bigoi Like '1710608%' --KTM\n" +
                                    "            Or bigoi Like '02313%' Or bigoi Like '17102313%' Or bigoi Like '02318%' Or bigoi Like '17102318%' --LCU\n" +
                                    "            Or bigoi Like '02303%' Or bigoi Like '17102303%' Or bigoi Like '02308%' Or bigoi Like '17102308%' --DBN\n" +
                                    "            Or bigoi Like '0723%'  Or bigoi Like '1710723%'  Or bigoi Like '0728%'  Or bigoi Like '1710728%' --LAN\n" +
                                    "            Or bigoi Like '0203%'  Or bigoi Like '1710203%'  Or bigoi Like '0208%'  Or bigoi Like '1710208%' --LCI\n" +
                                    "            Or bigoi Like '0633%'  Or bigoi Like '1710633%'  Or bigoi Like '0638%'  Or bigoi Like '1710638%' --LDG\n" +
                                    "            Or bigoi Like '0253%'  Or bigoi Like '1710253%'  Or bigoi Like '0258%'  Or bigoi Like '1710258%' --LSN\n" +
                                    "            Or bigoi Like '03503%' Or bigoi Like '17103503%' Or bigoi Like '03508%' Or bigoi Like '17103508%' --NDH\n" +
                                    "            Or bigoi Like '0383%'  Or bigoi Like '1710383%'  Or bigoi Like '0388%'  Or bigoi Like '1710388%' --NAN\n" +
                                    "            Or bigoi Like '0303%'  Or bigoi Like '1710303%'  Or bigoi Like '0308%'  Or bigoi Like '1710308%' --NBH\n" +
                                    "            Or bigoi Like '0683%'  Or bigoi Like '1710683%'  Or bigoi Like '0688%'  Or bigoi Like '1710688%' --NTN\n" +
                                    "            Or bigoi Like '02103%' Or bigoi Like '17102103%' Or bigoi Like '02108%' Or bigoi Like '17102108%' --PTO\n" +
                                    "            Or bigoi Like '0573%'  Or bigoi Like '1710573%'  Or bigoi Like '0578%'  Or bigoi Like '1710578%' --PYN\n" +
                                    "            Or bigoi Like '0523%'  Or bigoi Like '1710523%'  Or bigoi Like '0528%'  Or bigoi Like '1710528%' --QBH\n" +
                                    "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --QNM\n" +
                                    "            Or bigoi Like '0553%'  Or bigoi Like '1710553%'  Or bigoi Like '0558%'  Or bigoi Like '1710558%' --QNI\n" +
                                    "            Or bigoi Like '0333%'  Or bigoi Like '1710333%'  Or bigoi Like '0338%'  Or bigoi Like '1710338%' --QNH\n" +
                                    "            Or bigoi Like '0533%'  Or bigoi Like '1710533%'  Or bigoi Like '0538%'  Or bigoi Like '1710538%' --QTI\n" +
                                    "            Or bigoi Like '0793%'  Or bigoi Like '1710793%'  Or bigoi Like '0798%'  Or bigoi Like '1710798%' --STG\n" +
                                    "            Or bigoi Like '0223%'  Or bigoi Like '1710223%'  Or bigoi Like '0228%'  Or bigoi Like '1710228%' --SLA\n" +
                                    "            Or bigoi Like '083%'   Or bigoi Like '171083%'   Or bigoi Like '088%'   Or bigoi Like '171088%' --HCM\n" +
                                    "            Or bigoi Like '0373%'  Or bigoi Like '1710373%'  Or bigoi Like '0378%'  Or bigoi Like '1710378%' --THA\n" +
                                    "            Or bigoi Like '0363%'  Or bigoi Like '1710363%'  Or bigoi Like '0368%'  Or bigoi Like '1710368%' --TBH\n" +
                                    "            Or bigoi Like '02803%' Or bigoi Like '17102803%' Or bigoi Like '02808%' Or bigoi Like '17102808%' --TNN\n" +
                                    "            Or bigoi Like '0543%'  Or bigoi Like '1710543%'  Or bigoi Like '0548%'  Or bigoi Like '1710548%' --HUE\n" +
                                    "            Or bigoi Like '0733%'  Or bigoi Like '1710733%'  Or bigoi Like '0738%'  Or bigoi Like '1710738%' --TGG\n" +
                                    "            Or bigoi Like '0743%'  Or bigoi Like '1710743%'  Or bigoi Like '0748%'  Or bigoi Like '1710748%' --TVH\n" +
                                    "            Or bigoi Like '0273%'  Or bigoi Like '1710273%'  Or bigoi Like '0278%'  Or bigoi Like '1710278%' --TQG\n" +
                                    "            Or bigoi Like '0663%'  Or bigoi Like '1710663%'  Or bigoi Like '0668%'  Or bigoi Like '1710668%' --TNH\n" +
                                    "            Or bigoi Like '0703%'  Or bigoi Like '1710703%'  Or bigoi Like '0708%'  Or bigoi Like '1710708%' --VLG\n" +
                                    "            Or bigoi Like '02113%' Or bigoi Like '17102113%' Or bigoi Like '02118%' Or bigoi Like '17102118%' --VPC\n" +
                                    "            Or bigoi Like '0293%'  Or bigoi Like '1710293%'  Or bigoi Like '0298%'  Or bigoi Like '1710298%' --YBI\n" +
                                    "            Or bigoi Like '05113%' Or bigoi Like '17105113%' Or bigoi Like '05118%' Or bigoi Like '17105118%' --DKG\n" +
                                    "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --DNG\n" +
                                    "            Or bigoi Like '0613%'  Or bigoi Like '1710613%'  Or bigoi Like '0618%'  Or bigoi Like '1710618%' --DNI\n" +
                                    "            Or bigoi Like '0673%'  Or bigoi Like '1710673%'  Or bigoi Like '0678%'  Or bigoi Like '1710678%' --DTP\n" +
                                    "            )\n" +
                                    " Union all\n" +
                                    "     select TGGOI,PHUT, doisoat.getmamang(doisoat.loaibo_so_0(doisoat.loaibo_so_171(bigoi))) Ma_Mang_BG\n" +
                                    "     from doisoat.ct_host_TanLoi_{0}_{1:D2}a_dlk\n" +
                                    "     where chugoi not in (select somay from doisoat.danhba_nghiepvu)\n" +
                                    "           And ma_mang='DLK' and(\n" +
                                    "                bigoi Like '0763%' Or bigoi Like '1710763%' Or bigoi Like '0768%' Or bigoi Like '1710768%' --AGG\n" +
                                    "            Or bigoi Like '0643%' Or bigoi Like '1710643%' Or bigoi Like '0648%' Or bigoi Like '1710648%' --VTU\n" +
                                    "            Or bigoi Like '06503%' Or bigoi Like '17106503%' Or bigoi Like '06508%' Or bigoi Like '17106508%' --BDG\n" +
                                    "            Or bigoi Like '06513%' Or bigoi Like '17106513%' Or bigoi Like '06518%' Or bigoi Like '17106518%' --BPC\n" +
                                    "            Or bigoi Like '0623%' Or bigoi Like '1710623%' Or bigoi Like '0628%' Or bigoi Like '1710628%' --BTN\n" +
                                    "            Or bigoi Like '0563%' Or bigoi Like '1710563%' Or bigoi Like '0568%' Or bigoi Like '1710568%' --BDH\n" +
                                    "            Or bigoi Like '07813%' Or bigoi Like '17107813%' Or bigoi Like '07818%' Or bigoi Like '17107818%' --BLU\n" +
                                    "            Or bigoi Like '02813%' Or bigoi Like '17102813%' Or bigoi Like '02818%' Or bigoi Like '17102818%' --BCN\n" +
                                    "            Or bigoi Like '02403%' Or bigoi Like '17102403%' Or bigoi Like '02408%' Or bigoi Like '17102408%' --BGG\n" +
                                    "            Or bigoi Like '02413%' Or bigoi Like '17102413%' Or bigoi Like '02418%' Or bigoi Like '17102418%' --BNH\n" +
                                    "            Or bigoi Like '0753%' Or bigoi Like '1710753%' Or bigoi Like '0758%' Or bigoi Like '1710758%' --BTE\n" +
                                    "            Or bigoi Like '0263%' Or bigoi Like '1710263%' Or bigoi Like '0268%' Or bigoi Like '1710268%' --CBG\n" +
                                    "            Or bigoi Like '07803%' Or bigoi Like '17107803%' Or bigoi Like '07808%' Or bigoi Like '17107808%' --CMU\n" +
                                    "            Or bigoi Like '07103%' Or bigoi Like '17107103%' Or bigoi Like '07108%' Or bigoi Like '17107108%' --CTO\n" +
                                    "            Or bigoi Like '07113%' Or bigoi Like '17107113%' Or bigoi Like '07118%' Or bigoi Like '17107118%' --HGG\n" +
                                    "            Or bigoi Like '0593%' Or bigoi Like '1710593%' Or bigoi Like '0598%' Or bigoi Like '1710598%' --GLI\n" +
                                    "            Or bigoi Like '02193%' Or bigoi Like '17102193%' Or bigoi Like '02198%' Or bigoi Like '17102198%' --HGG\n" +
                                    "            Or bigoi Like '03513%' Or bigoi Like '17103513%' Or bigoi Like '03518%' Or bigoi Like '17103518%' --HNM\n" +
                                    "            Or bigoi Like '043%' Or bigoi Like '171043%' Or bigoi Like '048%' Or bigoi Like '171048%' --HNI\n" +
                                    "            Or bigoi Like '0393%' Or bigoi Like '1710393%' Or bigoi Like '0398%' Or bigoi Like '1710398%' --HTH\n" +
                                    "            Or bigoi Like '02183%' Or bigoi Like '17102183%' Or bigoi Like '02188%' Or bigoi Like '17102188%' --HBH\n" +
                                    "            Or bigoi Like '03213%' Or bigoi Like '17103213%' Or bigoi Like '03218%' Or bigoi Like '17103218%' --HYN\n" +
                                    "            Or bigoi Like '03203%' Or bigoi Like '17103203%' Or bigoi Like '03208%' Or bigoi Like '17103208%' --HDG\n" +
                                    "            Or bigoi Like '0313%' Or bigoi Like '1710313%' Or bigoi Like '0318%' Or bigoi Like '1710318%' --HPG\n" +
                                    "            Or bigoi Like '0583%' Or bigoi Like '1710583%' Or bigoi Like '0588%' Or bigoi Like '1710588%' --KHA\n" +
                                    "            Or bigoi Like '0773%' Or bigoi Like '1710773%' Or bigoi Like '0778%' Or bigoi Like '1710778%' --KGG\n" +
                                    "            Or bigoi Like '0603%' Or bigoi Like '1710603%' Or bigoi Like '0608%' Or bigoi Like '1710608%' --KTM\n" +
                                    "            Or bigoi Like '02313%' Or bigoi Like '17102313%' Or bigoi Like '02318%' Or bigoi Like '17102318%' --LCU\n" +
                                    "            Or bigoi Like '02303%' Or bigoi Like '17102303%' Or bigoi Like '02308%' Or bigoi Like '17102308%' --DBN\n" +
                                    "            Or bigoi Like '0723%' Or bigoi Like '1710723%' Or bigoi Like '0728%' Or bigoi Like '1710728%' --LAN\n" +
                                    "            Or bigoi Like '0203%' Or bigoi Like '1710203%' Or bigoi Like '0208%' Or bigoi Like '1710208%' --LCI\n" +
                                    "            Or bigoi Like '0633%' Or bigoi Like '1710633%' Or bigoi Like '0638%' Or bigoi Like '1710638%' --LDG\n" +
                                    "            Or bigoi Like '0253%' Or bigoi Like '1710253%' Or bigoi Like '0258%' Or bigoi Like '1710258%' --LSN\n" +
                                    "            Or bigoi Like '03503%' Or bigoi Like '17103503%' Or bigoi Like '03508%' Or bigoi Like '17103508%' --NDH\n" +
                                    "            Or bigoi Like '0383%' Or bigoi Like '1710383%' Or bigoi Like '0388%' Or bigoi Like '1710388%' --NAN\n" +
                                    "            Or bigoi Like '0303%' Or bigoi Like '1710303%' Or bigoi Like '0308%' Or bigoi Like '1710308%' --NBH\n" +
                                    "            Or bigoi Like '0683%' Or bigoi Like '1710683%' Or bigoi Like '0688%' Or bigoi Like '1710688%' --NTN\n" +
                                    "            Or bigoi Like '02103%' Or bigoi Like '17102103%' Or bigoi Like '02108%' Or bigoi Like '17102108%' --PTO\n" +
                                    "            Or bigoi Like '0573%' Or bigoi Like '1710573%' Or bigoi Like '0578%' Or bigoi Like '1710578%' --PYN\n" +
                                    "            Or bigoi Like '0523%' Or bigoi Like '1710523%' Or bigoi Like '0528%' Or bigoi Like '1710528%' --QBH\n" +
                                    "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --QNM\n" +
                                    "            Or bigoi Like '0553%' Or bigoi Like '1710553%' Or bigoi Like '0558%' Or bigoi Like '1710558%' --QNI\n" +
                                    "            Or bigoi Like '0333%' Or bigoi Like '1710333%' Or bigoi Like '0338%' Or bigoi Like '1710338%' --QNH\n" +
                                    "            Or bigoi Like '0533%' Or bigoi Like '1710533%' Or bigoi Like '0538%' Or bigoi Like '1710538%' --QTI\n" +
                                    "            Or bigoi Like '0793%' Or bigoi Like '1710793%' Or bigoi Like '0798%' Or bigoi Like '1710798%' --STG\n" +
                                    "            Or bigoi Like '0223%' Or bigoi Like '1710223%' Or bigoi Like '0228%' Or bigoi Like '1710228%' --SLA\n" +
                                    "            Or bigoi Like '083%' Or bigoi Like '171083%' Or bigoi Like '088%' Or bigoi Like '171088%' --HCM\n" +
                                    "            Or bigoi Like '0373%' Or bigoi Like '1710373%' Or bigoi Like '0378%' Or bigoi Like '1710378%' --THA\n" +
                                    "            Or bigoi Like '0363%' Or bigoi Like '1710363%' Or bigoi Like '0368%' Or bigoi Like '1710368%' --TBH\n" +
                                    "            Or bigoi Like '02803%' Or bigoi Like '17102803%' Or bigoi Like '02808%' Or bigoi Like '17102808%' --TNN\n" +
                                    "            Or bigoi Like '0543%' Or bigoi Like '1710543%' Or bigoi Like '0548%' Or bigoi Like '1710548%' --HUE\n" +
                                    "            Or bigoi Like '0733%' Or bigoi Like '1710733%' Or bigoi Like '0738%' Or bigoi Like '1710738%' --TGG\n" +
                                    "            Or bigoi Like '0743%' Or bigoi Like '1710743%' Or bigoi Like '0748%' Or bigoi Like '1710748%' --TVH\n" +
                                    "            Or bigoi Like '0273%' Or bigoi Like '1710273%' Or bigoi Like '0278%' Or bigoi Like '1710278%' --TQG\n" +
                                    "            Or bigoi Like '0663%' Or bigoi Like '1710663%' Or bigoi Like '0668%' Or bigoi Like '1710668%' --TNH\n" +
                                    "            Or bigoi Like '0703%' Or bigoi Like '1710703%' Or bigoi Like '0708%' Or bigoi Like '1710708%' --VLG\n" +
                                    "            Or bigoi Like '02113%' Or bigoi Like '17102113%' Or bigoi Like '02118%' Or bigoi Like '17102118%' --VPC\n" +
                                    "            Or bigoi Like '0293%' Or bigoi Like '1710293%' Or bigoi Like '0298%' Or bigoi Like '1710298%' --YBI\n" +
                                    "            Or bigoi Like '05113%' Or bigoi Like '17105113%' Or bigoi Like '05118%' Or bigoi Like '17105118%' --DKG\n" +
                                    "            Or bigoi Like '05103%' Or bigoi Like '17105103%' Or bigoi Like '05108%' Or bigoi Like '17105108%' --DNG\n" +
                                    "            Or bigoi Like '0613%' Or bigoi Like '1710613%' Or bigoi Like '0618%' Or bigoi Like '1710618%' --DNI\n" +
                                    "            Or bigoi Like '0673%' Or bigoi Like '1710673%' Or bigoi Like '0678%' Or bigoi Like '1710678%' --DTP\n" +
                                    "            )\n" +
                                    "          )\n" +
                                    "group by Ma_Mang_BG";

                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "SQL_CO_DINH_DLK_GOI_DI", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "SQL_CO_DINH_DLK_GOI_DI", value);
                }

            }
        }

        public static string DS_178Viettel_QuocTe
        {
            get
            {
                var sql = @"SELECT sum ( CASE  WHEN tggoi >= 0 THEN 1 ELSE 0 END ) AS Block6 , 
                                    sum (CASE WHEN tggoi > 6 THEN tggoi - 6 ELSE 0 END ) AS Block1 
                                    FROM ct_host_810_{0}_{1:D2}a_dlk 
                                    WHERE bigoi LIKE '17800%' AND tggoi > 0";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "DS_178Viettel_QuocTe", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "DS_178Viettel_QuocTe", value);
                }

            }
        }
        
        public static string DS_178Viettel_TrongNuoc
        {
            get
            {
                var sql = @"SELECT sum ( CASE  WHEN tggoi >= 0 THEN 1 ELSE 0 END ) AS Block6 , 
                                    sum (CASE WHEN tggoi > 6 THEN tggoi - 6 ELSE 0 END ) AS Block1 
                                    FROM ct_host_810_{0}_{1:D2}a_dlk 
                                    WHERE bigoi LIKE '1780%' AND bigoi NOT LIKE '17800%' AND tggoi > 0";
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "DS_178Viettel_TrongNuoc", sql);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "DS_178Viettel_TrongNuoc", value);
                }

            }
        }

        public static string DNK_Route
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "DNK_Route", "177C,VTEL,178C,178O,VPCO,179C,VP17,178I,FPTR");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "DNK_Route", value);
                }

            }
        }
       
        public static string VTN_Route
        {
            get
            {
                return Data.DoiSoat_SQL.GetValue("DOI_SOAT", "VTN_Route", "DNTL,NGNC,HCMA,HCMC,DN1D,DN2D,171D,DTL1,DTL2,NGNI,NGNO,NGNH,CONG,BCHU,BPHO,NGND");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Data.DoiSoat_SQL.SetValue("DOI_SOAT", "VTN_Route", value);
                }

            }
        }
       
        public static string Folder_TANLOI
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "Folder_TANLOI", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "Folder_TANLOI", value);
                }

            }
        }
       
        public static string Folder_BMT
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "Folder_BMT", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "Folder_BMT", value);
                }
            }
        }
       
        public static string Folder_LUU
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "Folder_LUU", Application.StartupPath);
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "Folder_LUU", value);
                }
            }
        }
       
        public static int THANG
        {
            get
            {

                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "THANG", DateTime.Now.Month);
            }
            set
            {
                if (value != 0)
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "THANG", value);
                }
            }
        }
      
        public static int NAM
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "NAM", DateTime.Now.Year);
            }
            set
            {
                if (value != 0)
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "NAM", value);
                }
            }
        }
        public static string NGUOILAP_BAOCAO
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "NGUOILAP_BAOCAO", "Nguyễn Vĩnh Thuyên");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "NGUOILAP_BAOCAO", value);
                }
            }
        }
        #region FTP Infomation
     
        public static string FTP_SERVER
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "FTP_SERVER", "10.147.30.10");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "FTP_SERVER", value);
                }

            }
        }
     
        public static string FTP_USER
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "FTP_USER", "dlk");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "FTP_USER", value);
                }

            }
        }
     
        public static string FTP_PASS
        {
            get
            {
                Xml xml = new Xml();
                var t = xml.GetValue("DOI_SOAT", "FTP_PASS", "");
                return VNPT.Utility.Security.EncryptandDecrypt.Decrypt(t); 
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "FTP_PASS", VNPT.Utility.Security.EncryptandDecrypt.Encrypt(value));
                }
            }
        }

        public static string FTP_CURRENT_PATH
        {
            get
            {
                Xml xml = new Xml();
                return xml.GetValue("DOI_SOAT", "FTP_CURRENT_PATH", "");
            }
            set
            {
                if (!string.IsNullOrWhiteSpace(value))
                {
                    Xml xml = new Xml();
                    xml.SetValue("DOI_SOAT", "FTP_CURRENT_PATH", value);
                }

            }
        }
        #endregion
    }
}
